﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using famails.common;
using famails.dto;
using famails.App_Code.source.dto;

namespace famails.dao
{
    /// <summary>
    /// Summary description for CM_CustomerCareDAO
    /// </summary>
    public class CM_CustomerCareDAO
    {
        public DataTable GetByID(decimal ID, int UserID)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_CustomerCare_Get4Edit", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CustomerCareID", SqlDbType.Decimal).Value = ID;
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable Search(string subject, int groupid, string from, string to, int userid)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_CustomerCare_Search", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@subject", SqlDbType.NVarChar).Value = subject;
            cmd.Parameters.Add("@from", SqlDbType.NVarChar).Value = from;
            cmd.Parameters.Add("@To", SqlDbType.NVarChar).Value = to;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
            cmd.Parameters.Add("@groupid", SqlDbType.NVarChar).Value = groupid;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public object Insert(CM_CustomerCareDTO dto)
        {
            SqlCommand cmd = new SqlCommand(@"INSERT INTO [tblCM_CustomerCare]
                                       ([Subject]
                                       ,[Body]
                                       ,[StartDate]
                                       ,[GroupID]
                                       ,[MailConfigID]
                                       ,[CreateBy], createdate)
                                 VALUES
                                       (@Subject
                                       ,@Body
                                       ,@StartDate
                                       ,@GroupID
                                       ,@MailConfigID
                                       ,@CreateBy, getdate()) select @@identity", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = dto.Subject;
            cmd.Parameters.Add("@Body", SqlDbType.NVarChar).Value = dto.Body;
            cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = dto.StartDate;
            cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = dto.GroupID;
            cmd.Parameters.Add("@MailConfigID", SqlDbType.Int).Value = dto.MailConfigID;
            cmd.Parameters.Add("@CreateBy", SqlDbType.Int).Value = dto.createBy;
            object i = cmd.ExecuteScalar();
            cmd.Dispose();
            return i;
        }

        public bool Edit(CM_CustomerCareDTO dto)
        {
            SqlCommand cmd = new SqlCommand(@"update tblCM_CustomerCare set [Amount]=@Amount
                                       ,[Subject]=@Subject
                                       ,[Body]=@Body
                                       ,[StartDate]=@StartDate
                                       ,[GroupID]=@GroupID
                                       ,[MailConfigID]=@MailConfigID
                                        where CustomerCareID = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = dto.Subject;
            cmd.Parameters.Add("@Body", SqlDbType.NVarChar).Value = dto.Body;
            cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = dto.StartDate;
            cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = dto.GroupID;
            cmd.Parameters.Add("@MailConfigID", SqlDbType.Int).Value = dto.MailConfigID;
            cmd.Parameters.Add("@CreateBy", SqlDbType.Int).Value = dto.createBy;
            cmd.Parameters.Add("@Id", SqlDbType.Decimal).Value = dto.CustomerCareID;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        public bool Delete(int id)
        {
            SqlCommand cmd = new SqlCommand("update tblCM_CustomerCare set isdelete=1 where CustomerCareID = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }



        internal void AddSendMailByCustomerCareID(decimal CustomerCareID, int UserID)
        {
            string sql = "SP_AddSendMailFromCustomerCareId";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CustomerCareID", SqlDbType.Int).Value = CustomerCareID;
            cmd.Parameters.Add("@userId", SqlDbType.Int).Value = UserID;
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            cmd.ExecuteNonQuery();
        }


        internal DataTable GetReportByRegisterId(string CustomerCareId)
        {
            SqlCommand cmd = new SqlCommand("[SP_CM_CustomerCare_ReportById]", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CustomerCareId", SqlDbType.Int).Value = CustomerCareId;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        internal DataTable GetErrorMailList(decimal id)
        {
            SqlCommand cmd = new SqlCommand("SP_GetErrorMailList_New", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Id", SqlDbType.Decimal).Value = id;
            cmd.Parameters.Add("@Event1_SendRegister2_CustomerCare3", SqlDbType.Int).Value = 3;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        internal DataTable GetOpenEmailList(string CustomerCareId)
        {
            SqlCommand cmd = new SqlCommand("Select id,[to] email, sendtime, OpenDate,MessageId From tblSendMail where CustomerCareId = @id and isopen=1", ConnectionData._MyConnection);
            cmd.Parameters.Add("@Id", SqlDbType.Decimal).Value = CustomerCareId;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
    }
}
